﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb;

namespace ExcelTools.OleDB
{
    public class ExcelOLETinkering
    {
        private OleDbDataReader cur_dr;
        private DataTable cur_dt;
        private OleDbCommand cur_command;
        private OleDbConnection connection;
        private string src_filepath;

        public ExcelOLETinkering()
        {
            src_filepath = String.Empty;
        }

        ~ExcelOLETinkering()
        {
            if (connection != null && connection.State != ConnectionState.Closed)
                connection.Close();
        }

        public ExcelOLETinkering(string _src_filepath)
        {
            src_filepath = _src_filepath;
        }

        public string SrcFilepath
        {
            get { return src_filepath; }
            set { src_filepath = value; }
        }

        public OleDbDataReader CurrentDataReader
        {
            get { return cur_dr; }
        }

        public DataTable CurrentDataTable
        {
            get { return cur_dt; }
        }

        public OleDbCommand CurrentCommand
        {
            get { return cur_command; }
        }

        public OleDbConnection Connection
        {
            get { return connection; }
        }

        public void OpenConnection()
        {
            if (connection == null)
                connection = new OleDbConnection(GetConnectionString());

            Connection.Open();
        }

        public System.Threading.Tasks.Task OpenConnectionAsync()
        {
            if (connection == null)
                connection = new OleDbConnection(GetConnectionString());

            return Connection.OpenAsync();
        }

        public void ExecuteQuery(string query)
        {
            if (Connection == null)
                throw new InvalidOperationException("Cannot execute a query because the OLEDB connection is null");
            else if (Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Cannot execute a query because the OLEDB connection state != ConnectionState.Open");

            cur_command = new OleDbCommand(query, Connection);
            cur_dt = new DataTable();
            cur_dr = CurrentCommand.ExecuteReader();

            if (cur_dr.Read())
                cur_dt.Load(cur_dr);
        }

        public string GetConnectionString()
        {
            return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SrcFilepath + @";Extended Properties=Excel 12.0;"; 
        }
    }
}
